package thesis.database;

import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import org.springframework.jdbc.core.JdbcTemplate;

import com.sun.corba.se.impl.ior.NewObjectKeyTemplateBase;

public class TaxiJDBCTemplate implements TaxiDAO {

	private DataSource dataSource;
	private JdbcTemplate jdbcTemplateObject;

	@Override
	public void setDataSource(DataSource ds) {
		this.dataSource = ds;
		this.jdbcTemplateObject = new JdbcTemplate(dataSource);

	}

	@Override
	public void create(Taxi t) {

		String sql = "insert into taxi (imei,number_of_seat,number_plate,"
				+ "description,status,newest_latitude,newest_longitude,expiration_time,display_color,"
				+ "sim_number,ip_address,port_listen,is_active, creation_time,"
				+ "last_update,gps_data,driver_name,"
				+ "driver_phone,driver_email, driver_birthday,"
				+ "driver_license_type, driver_expire_day,"
				+ "driver_license_code,  driver_shift_number,"
				+ "driver_day_start, driver_identify_number) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
		jdbcTemplateObject.update(sql, t.getImei(), t.getNumberOfSeat(),
				t.getNumberPlate(), t.getDescription(), t.getStatus(),
				t.getNewestLatitude(), t.getNewestLongitude(),
				t.getExpirationTime(), t.getDisplayColor(), t.getSimNumber(),
				t.getIpAddress(), t.getPortListen(), t.isActive(),
				t.getCreationTime(), t.getLastUpdate(), t.getGpsData(),
				t.getDriverName(), t.getDriverPhone(), t.getDriverEmail(),
				t.getDriverBirthday(), t.getDriverLicenseType(),
				t.getDriverExpireDay(), t.getDriverLicenseCode(),
				t.getDriverShiftNumber(), t.getDriverDateStart(),
				t.getDriverIdentifyNumber());
		System.out.println("Created Taxi Record");

	}

	@Override
	public Taxi getTaxi(Integer id) {
		Taxi taxi;
		String sql = "select * from taxi where taxi_id = ?";
		try {
			taxi = jdbcTemplateObject.queryForObject(sql, new Object[] { id },
					new TaxiMapper());
		} catch (Exception e) {
			return null;
		}
		return taxi;
	}

	@Override
	public List<Taxi> getTaxiesByNumber(String numberPlate) {
		List<Taxi> taxies = null;
		String sql = "select * from taxi where number_plate like ?";
		try {
			taxies = jdbcTemplateObject.query(sql, new TaxiMapper(), "%"
					+ numberPlate + "%");
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}

		return taxies;
	}

	@Override
	public List<Taxi> listTaxies() {
		String sql = "select * from taxi";
		List<Taxi> list = jdbcTemplateObject.query(sql, new TaxiMapper());
		return list;
	}

	@Override
	public List<Integer> getListTaxiIdByBoundingCoordinates(double minLat,
			double minLon, double maxLat, double maxLon, int numberOfSeat) {

		// List<Taxi> list = jdbcTemplateObject.query(sql, new Object[] {
		// maxLon,
		// maxLat, minLon, minLat, numberOfSeat }, new TaxiMapper());
		// String sql =
		// "select taxi_id from taxi where cast(newest_longitude as decimal(15,9)) <= "
		// + maxLon
		// + " and cast(newest_latitude as decimal(15,9)) <= "
		// + maxLat
		// + " and cast(newest_longitude as decimal(15,9)) >= "
		// + minLon
		// + " and cast(newest_latitude as decimal(15,9))>= "
		// + minLat + " and number_of_seat >= " + numberOfSeat;
		// System.out.println(sql);
		String sql = "select taxi_id from taxi where cast(newest_longitude as decimal(15,9)) <= ? "
				+ "and cast(newest_latitude as decimal(15,9)) <= ? and cast(newest_longitude as decimal(15,9)) >= ? "
				+ "and cast(newest_latitude as decimal(15,9))>= ? and number_of_seat >= ?";
		List<Integer> list = jdbcTemplateObject.queryForList(sql, new Object[] {
				maxLon, maxLat, minLon, minLat, numberOfSeat }, Integer.class);
		return list;
	}

	/*
	 * public ArrayList<Integer> getListTaxiIdByBoundingCoordinates(double
	 * minLat, double minLon, double maxLat, double maxLon, int numberOfSeat) {
	 * String sql =
	 * "select taxi_id from taxi where cast(newest_longitude as decimal) <= ? "
	 * +
	 * "and cast(newest_latitude as decimal) <= ? and cast(newest_longitude as decimal) >= ? "
	 * + "and cast(newest_latitude as decimal) >= ? and number_of_seat >= ?"; //
	 * List<Taxi> list = jdbcTemplateObject.query(sql, new Object[] { maxLon, //
	 * maxLat, minLon, minLat, numberOfSeat }, new TaxiMapper()); List<Taxi>
	 * list = jdbcTemplateObject.query(sql, new Object[] { maxLon, maxLat,
	 * minLon, minLat, numberOfSeat }, new TaxiMapper()); ArrayList<Integer> ids
	 * = new ArrayList<Integer>(); for (Taxi taxi : list) {
	 * ids.add(taxi.getTaxiId()); } return ids; }
	 */

	@Override
	public String getNumberPlateById(Integer taxiId) {
		Taxi taxi;
		String sql = "select * from taxi where taxi_id = ?";
		try {
			taxi = jdbcTemplateObject.queryForObject(sql,
					new Object[] { taxiId }, new TaxiMapper());
		} catch (Exception e) {
			return null;
		}
		// return taxi;

		return taxi.getNumberPlate();

	}

	@Override
	public String getNumberPlateByImei(String imei) {
		Taxi taxi;
		String sql = "select * from taxi where imei = ?";
		try {
			taxi = jdbcTemplateObject.queryForObject(sql,
					new Object[] { imei }, new TaxiMapper());
		} catch (Exception e) {
			return null;
		}
		// return taxi;

		return taxi.getNumberPlate();

	}

	@Override
	public void delete(Integer id) {
		String sql = "delete from taxi where taxi_id = ?";
		jdbcTemplateObject.update(sql, id);
		System.out.println("Deleted Taxi Record with ID = " + id);

	}

	@Override
	public void update(Taxi t) {
		String sql = "update taxi set imei = ?,number_of_seat = ?,number_plate = ?,"
				+ "description = ? ,status = ?,newest_latitude = ?,newest_longitude = ?,expiration_time = ?,display_color = ?,"
				+ "sim_number = ? , ip_address = ? ,port_listen = ?,is_active = ?, creation_time = ?,"
				+ "last_update = ?,gps_data = ?,driver_name = ?,"
				+ "driver_phone = ?,driver_email = ?, driver_birthday = ?,"
				+ "driver_license_type = ?, driver_expire_day = ?,"
				+ "driver_license_code = ?,  driver_shift_number = ?,"
				+ "driver_day_start = ?, driver_identify_number = ? where taxi_id = ?";
		jdbcTemplateObject.update(sql, t.getImei(), t.getNumberOfSeat(),
				t.getNumberPlate(), t.getDescription(), t.getStatus(),
				t.getNewestLatitude(), t.getNewestLongitude(),
				t.getExpirationTime(), t.getDisplayColor(), t.getSimNumber(),
				t.getIpAddress(), t.getPortListen(), t.isActive(),
				t.getCreationTime(), t.getLastUpdate(), t.getGpsData(),
				t.getDriverName(), t.getDriverPhone(), t.getDriverEmail(),
				t.getDriverBirthday(), t.getDriverLicenseType(),
				t.getDriverExpireDay(), t.getDriverLicenseCode(),
				t.getDriverShiftNumber(), t.getDriverDateStart(),
				t.getDriverIdentifyNumber(), t.getTaxiId());
		System.out.println("Updated Taxi Record with ID = " + t.getTaxiId());

	}

	@Override
	public int getPortById(int id) {
		int port = -1;
		String sql = "select port_listen from taxi where taxi_id = ?";
		port = jdbcTemplateObject.queryForInt(sql, new Object[] { id });
		return port;
	}

	@Override
	public String getImeiById(int id) {
		String imei = "";
		String sql = "select imei from taxi where taxi_id = ?";
		imei = jdbcTemplateObject.queryForObject(sql, new Object[] { id },
				String.class);
		return imei;
	}

	@Override
	public void updateByImei(String imei, int status, String longitude,
			String latitude) {
		String sql = "update taxi set status = ?,newest_latitude = ?,newest_longitude = ? where imei = ?";
		jdbcTemplateObject.update(sql, status, latitude, longitude, imei);
		System.out.println("Updated Taxi Record with imei = " + imei);

	}

	@Override
	public Taxi getTaxiByImei(String imei) {
		Taxi taxi;
		String sql = "select * from taxi where imei = ?";
		try {
			taxi = jdbcTemplateObject.queryForObject(sql,
					new Object[] { imei }, new TaxiMapper());
		} catch (Exception e) {
			return null;
		}
		return taxi;
	}

}
